using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Windows.Forms;
using System.Runtime.InteropServices; // DllImport
using System.Security.Principal; // WindowsImpersonationContext
using System.Security.Permissions; // PermissionSetAttribute
using System.Threading;
using System.IO;
using System.Reflection;
using System.Resources;
using System.Drawing;
using System.Xml;
using GeneratorLibrary.Settings;
using GeneratorLibrary.Client.Base;

namespace GeneratorLibrary.CrudGeneratorLibrary {
    public partial class CRUDForm : Form {
        #region factory stuff
        IClientFactory factory;

        bool initialLoadFinished = false;

        private IClientFactory GetClientFactory(string generatorPath) {
            if (!String.IsNullOrEmpty(generatorPath)) {
                Type t;
                try {
                    //possible TypeLoadException
                    t = Type.GetType(generatorPath, false, false);

                    //possible InvalidCastException. 
                    return (IClientFactory)Activator.CreateInstance(t);

                } catch (TypeLoadException ex) {
                    throw new Exception("Selected 'Class Generator Type' not found", ex);
                } catch (InvalidCastException ex) {
                    throw new Exception("Selected 'Class Generator Type' does not implement IClientFactory.", ex);
                } catch (Exception ex) {
                    throw new Exception("Selected 'Class Generator Type' through an error during GetType or during Instantiation.", ex);
                }
            } else if (factory == null) {
                //this "can't" actually happen since we force there to be a default in the settings...
                // unless they manually change their settings file.
                throw new Exception("No default 'Class Generator Type' has been set.");
            } else {
                //they passed in blank, so we won't change it.
                return factory;
            }
        }
        private void LoadFactoryList() {
            List<KeyValuePair<string, string>> factories = new List<KeyValuePair<string, string>>();
            string[] ary = Addin.Default.ClassGenerators.Split(new char[] { ',' });
            foreach (string item in ary) {
                string[] pair = item.Split(new char[] { '=' });
                factories.Add(new KeyValuePair<string, string>(pair[1].Trim(), pair[0].Trim()));
            }
            ddlFactory.DisplayMember = "Value";
            ddlFactory.ValueMember = "Key";
            ddlFactory.DataSource = factories;

            factory = GetClientFactory(Addin.Default.DefaultClassGenerator);
            ddlFactory.SelectedValue = Addin.Default.DefaultClassGenerator;

            SetAllowsRunningScripts();
        }

        private void ddlFactory_SelectedIndexChanged(object sender, EventArgs e) {
            if (initialLoadFinished) {
                string factoryType = ddlFactory.SelectedValue.ToString();
                if (factoryType != factory.GetType().FullName) {
                    factory = GetClientFactory(factoryType);
                    txtServer.Text = "";
                    bindDatabases(false);
                }
                SetAllowsRunningScripts();
            }
        }
        #endregion
        private void SetAllowsRunningScripts() {
            bool execute = factory.AllowsRunningScripts;
            btnExecuteGrantAccess.Enabled = execute;
            btnExecuteStoredProcedures.Enabled = execute;
        }



        #region Member Variables
        string ConnectionString;
        StringBuilder ErrorLog, SuccessLog;
        #endregion

        #region connection string methods

        bool CreateConnectionString() {
            return CreateConnectionString(true);
        }

        bool CreateConnectionString(bool throwErrorIfNoServerOrDatabase) {
            if (txtServer.Text.Length > 0 && txtDatabase.Text.Length > 0) {
                ConnectionString = GetConnectionString(txtDatabase.Text);
                return true;
            } else if (throwErrorIfNoServerOrDatabase){
                System.Media.SystemSounds.Beep.Play();
                MessageBox.Show("Must enter both server and database;");
            }
            return false;
        }
        #endregion

        #region Form Load / Constructor
        public CRUDForm() {
            InitializeComponent();
            SettingsHelper.Initialize();

        }
        private void Form1_Load(object sender, EventArgs e) {
            LoadFactoryList();

            string name = System.Security.Principal.WindowsIdentity.GetCurrent().Name.Replace(".", " ");
            name = name.Substring(name.IndexOf('\\') + 1);
            this.txtAuthor.Text = Thread.CurrentThread.CurrentCulture.TextInfo.ToTitleCase(name.ToLower());


            txtServer.DataSource = Addin.Default["DatabaseServerList"].ToString().Split(new char[] { ',' });
            txtServer.Text = Addin.Default["DatabaseServer"].ToString();

            if (!String.IsNullOrEmpty(txtServer.Text))
                bindDatabases(false);

            txtOrderBy.Text = Addin.Default["OrderByFields"].ToString();
            txtIsActive.Text = Addin.Default["IsActiveField"].ToString();

            List<string> access = new List<string>(Addin.Default["GrantRoles"].ToString().Split(new char[] { ',', ';', '\r', '\n', ' ' }, StringSplitOptions.RemoveEmptyEntries));
            access.Insert(0, "");

            cboGrantAccess.DataSource = access;
            cboGrantAccess2.DataSource = access.ToArray();

            InitializeCodeSnippets();

            initialLoadFinished = true;
        }

        
        private void InitializeCodeSnippets() {
            List<KeyValuePair<string, string>> codeSnippets;
            XmlDocument document = new XmlDocument();
            using (Stream stream = Assembly.GetExecutingAssembly().GetManifestResourceStream("GeneratorLibrary.CrudGeneratorLibrary.CodeSnippets.xml")) {
                document.Load(stream);
            }

            codeSnippets = new List<KeyValuePair<string, string>>(document.ChildNodes[1].ChildNodes.Count + 1);
            codeSnippets.Add(new KeyValuePair<string, string>("", ""));

            foreach (XmlNode node in document.ChildNodes[1].ChildNodes) {
                string name = node.Attributes["name"].Value;
                string value = node.ChildNodes[0].Value;

                codeSnippets.Add(new KeyValuePair<string, string>(name, value));
                //cmbSnippets.Items.Add(name);
            }

            cmbSnippets.DisplayMember = "Key";
            cmbSnippets.ValueMember = "Value";
            cmbSnippets.DataSource = codeSnippets;
        }
        #endregion

        #region Methods that call to the dac. also append to logs
        string LF = Environment.NewLine;

        private string ExecuteNewProc(string sql, string name, CrudTableBase table, BaseDAC dac, bool ScriptOnly) {
            if (sql.Length > 0) {
                try {
                    if (!ScriptOnly)
                        dac.Execute(sql, null);
                    
                    SuccessLog.Append(sql);
                    SuccessLog.Append(LF + factory.GetGOStatement() + LF + LF);
                } catch (Exception ex) {
                    ErrorLog.Append("--");
                    ErrorLog.Append(ex.Message.Replace("\r"," ").Replace("\n"," "));
                    ErrorLog.Append(LF);
                    ErrorLog.Append(sql);
                    ErrorLog.Append(LF + factory.GetGOStatement() + LF + LF);
                    return name + ", ";
                }

                if (table.CurrentGrantSql.Length > 0) {
                    try {
                        if (!ScriptOnly)
                            dac.Execute(table.CurrentGrantSql, null);

                        SuccessLog.Append(table.CurrentGrantSql);
                        SuccessLog.Append(LF + factory.GetGOStatement() + LF + LF);
                    } catch (Exception ex) {

                        ErrorLog.Append("--");
                        ErrorLog.Append(ex.Message.Replace("\r", " ").Replace("\n", " "));
                        ErrorLog.Append(LF);
                        ErrorLog.Append(table.CurrentGrantSql);
                        ErrorLog.Append(LF + factory.GetGOStatement() + LF + LF);
                    }
                }
            }
            return "";

        }
        private DataTable GetColumns() {
            return factory.NewClassGenDAC(ConnectionString, txtDatabase.Text).GetColumns(this.txtTableName.Text);
            
        }
        #endregion

        #region Make Stored Procedures BUTTON


        private void btnExecuteStoredProcedures_Click(object sender, EventArgs e) {
            MakeStoredProcedures(false);
        }

        private void btnScriptStoredProcedures_Click(object sender, EventArgs e) {
            MakeStoredProcedures(true);
        }

        private void MakeStoredProcedures(bool ScriptOnly) {
            this.txtOrderBy.Text = this.txtOrderBy.Text.Replace(" ", ",").Replace(" \r\n", ",").Replace(",,",",");

            if (!CreateConnectionString()) {
                return;
            }
            DataTable dt = GetColumns();
            List<CrudTableBase> tables = this.factory.GetCrudTables(dt);
            string errors = "";
            SuccessLog = new StringBuilder();
            ErrorLog = new StringBuilder();

            using (BaseDAC dac = factory.NewClassGenDAC(ConnectionString, txtDatabase.Text)) {
                foreach (CrudTableBase table in tables) {
                    table.Author = this.txtAuthor.Text;
                    table.IsActive = this.txtIsActive.Text;
                    table.OrderBy = this.txtOrderBy.Text;
                    table.GrantAccess = cboGrantAccess.Text;
                    table.Database = txtDatabase.Text;
                    table.ProcDatabase = txtDatabase.Text;
                    string errorLine = "";

                    if (this.chkCreate.Checked)
                        errorLine = errorLine + ExecuteNewProc(table.GenerateCreate(), "Create", table, dac, ScriptOnly);
                    if (this.chkDelete.Checked)
                        errorLine = errorLine + ExecuteNewProc(table.GenerateDelete(), "Delete", table, dac, ScriptOnly);
                    if (this.chkUpdate.Checked)
                        errorLine = errorLine + ExecuteNewProc(table.GenerateUpdate(), "Update", table, dac, ScriptOnly);
                    if (this.chkRead.Checked)
                        errorLine = errorLine + ExecuteNewProc(table.GenerateRead(), "Read", table, dac, ScriptOnly);

                    if (errorLine.Length > 0) {
                        if (errors.Length > 0)
                            errors = errors + Environment.NewLine;
                        errors = errors + table.TableName + "-" + errorLine.Remove(errorLine.Length - 2);//remove ", "
                    }
                }
            }
            if (errors.Length > 0)
                MessageBox.Show("The following procedures were not able to be generated:" + Environment.NewLine + errors);
            else {
                MessageBox.Show("SUCCESS");
            }
            this.txtSuccessLog.Text = SuccessLog.ToString();
            this.txtErrorLog.Text = ErrorLog.ToString();
            ErrorLog = null;
            SuccessLog = null;
        
        }
        #endregion
        #region bind databases
        private void bindDatabases() {
            bindDatabases(true);
        }
        private void bindDatabases(bool showError) {
            string table1 = txtTableName.Text;
            string proc = cboProcLike.Text;


            this.txtDatabase.DataSource = null;
            this.txtTableName.DataSource = null;
            this.txtTableName2.DataSource = null;
            this.cboProcLike.DataSource = null;
            txtServer.Text = txtServer.Text.Trim();
            try {
                if (this.txtServer.Text.Length > 0) {

                    using (BaseDAC dac = factory.NewClassGenDAC(GetConnectionString(factory.CatalogSchema), factory.CatalogSchema)) {
                        this.txtDatabase.DataSource = dac.GetDatabases();

                        List<string> DatabaseServerList = new List<string>(Addin.Default["DatabaseServerList"].ToString().Split(new char[] { ',' }));
                        if (!DatabaseServerList.Contains(txtServer.Text)) {
                            DatabaseServerList.Add(txtServer.Text);
                            DatabaseServerList.Sort();
                            Addin.Default["DatabaseServerList"] = String.Join(",", DatabaseServerList.ToArray());
                            Addin.Default.Save();
                            string oldserver = txtServer.Text;
                            txtServer.DataSource = DatabaseServerList;
                            txtServer.Text = oldserver;
                        }
                    }
                }
            } catch (Exception ex){
                if (showError)
                    MessageBox.Show("Login Failed: " + ex.Message, "Login Failed", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            txtTableName.Text = table1;
            cboProcLike.Text = proc;
        }

        #endregion
        #region Script Inserts
        private void btnScriptInserts_Click(object sender, EventArgs e) {
            if (!CreateConnectionString()) {
                return;
            }
            if (this.txtTableName2.SelectedItem == null || this.txtTableName2.Text.ToUpper() != this.txtTableName2.SelectedValue.ToString().ToUpper()) {
                System.Media.SystemSounds.Beep.Play();
                MessageBox.Show("Must select a table.");
                return;
            }
            using (BaseDAC dac = factory.NewClassGenDAC(GetConnectionString(txtDatabase.Text), txtDatabase.Text)) {
                this.txtSuccessLog.Text = dac.GetInserts(this.txtTableName2.Text, this.chkDisableIdentity.Checked);
                this.txtErrorLog.Text = "";
            }
        }
        #endregion
        #region grant access
        private void GrantAccess(bool ScriptOnly) {
            if (!CreateConnectionString()) {
                return;
            }
            if (this.cboGrantAccess2.Text.Length == 0) {
                System.Media.SystemSounds.Beep.Play();
                MessageBox.Show("Must select access to grant.");
                return;
            }

            SuccessLog = new StringBuilder();
            ErrorLog = new StringBuilder();

            List<string> list;
            using (BaseDAC dac = factory.NewClassGenDAC(ConnectionString, txtDatabase.Text)) {
                list = dac.GetProcs(this.cboProcLike.Text);

                foreach (string item in list) {
                    string sql = "grant execute on " + item + " to " + this.cboGrantAccess2.Text;
                    try {
                        if (!ScriptOnly)
                            dac.Execute(sql, null);

                        SuccessLog.Append(sql);
                        SuccessLog.Append("\r\n");
                    } catch (Exception ex) {
                        ErrorLog.Append("--");
                        ErrorLog.Append(ex.Message);
                        ErrorLog.Append("\r\n");
                        ErrorLog.Append(sql);
                        ErrorLog.Append("\r\n\r\n");
                    }
                }
            }

            this.txtSuccessLog.Text = SuccessLog.ToString();
            this.txtErrorLog.Text = ErrorLog.ToString();
            ErrorLog = null;
            SuccessLog = null;
        }

        private void btnExecuteGrantAccess_Click(object sender, EventArgs e) {
            GrantAccess(false);
        }
        private void btnScriptGrantAccess_Click(object sender, EventArgs e) {
            GrantAccess(true);
        }
        #endregion

        private void txtSuccessLog_KeyUp(object sender, KeyEventArgs e) {
            if (e.Control && !e.Alt && !e.Shift && e.KeyValue == 65) {
                txtSuccessLog.SelectAll();
            }
        }

        private void txtErrorLog_KeyUp(object sender, KeyEventArgs e) {
            if (e.Control && !e.Alt && !e.Shift && e.KeyValue == 65) {
                txtErrorLog.SelectAll();
            }
        }


        private void btnSetDatabase_Click(object sender, EventArgs e) {
            if (txtDatabase.SelectedIndex >= 0) {
                try {
                    BindTables();
                    BindProcedures();
                } catch (Exception ex) {
                    MessageBox.Show("You don't have access to the database '" + txtDatabase.Text + "'. Exception Details: " + ex.Message, "Database Restriction", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
        }

        private void BindTables() {
            string table = null;

            if (!CreateConnectionString(false)) {
                return;
            }

            //stupid combo-boxes. sometimes they'll have a selected value even when it's a custom value. grrr.
            if (this.txtTableName.SelectedItem == null || this.txtTableName.Text != this.txtTableName.SelectedValue.ToString())
                table = this.txtTableName.Text;

            BaseDAC dac = factory.NewClassGenDAC(ConnectionString, txtDatabase.Text);
            List<string> tableList = dac.GetTables();
            this.txtTableName.DataSource = tableList;

            //copy it, otherwise the lists become synchronized
            this.txtTableName2.DataSource = tableList.ToArray();

            if (table != null)
                this.txtTableName.Text = table;
        }
        private void BindProcedures() {
            string proc = null;

            if (!CreateConnectionString(false)) {
                return;
            }

            //stupid combo-boxes. sometimes they'll have a selected value even when it's a custom value. grrr.
            if (this.cboProcLike.SelectedItem == null || this.cboProcLike.Text != this.cboProcLike.SelectedValue.ToString())
                proc = this.cboProcLike.Text;



            BaseDAC dac = factory.NewClassGenDAC(ConnectionString, txtDatabase.Text);
            List<string> list = dac.GetProcs();

            this.cboProcLike.DisplayMember = "ProcName";
            this.cboProcLike.DataSource = list;

            if (proc != null)
                this.cboProcLike.Text = proc;
        }

        private void cmbSnippets_SelectedIndexChanged(object sender, EventArgs e) {
            if (cmbSnippets.SelectedValue != null && cmbSnippets.SelectedValue.ToString() != "")
                txtSuccessLog.Text = cmbSnippets.SelectedValue.ToString().Trim(new char[] { '\r', '\n' }).Replace("\t", "  ");
        }

        private void btnSettings_Click(object sender, EventArgs e) {
            ChangeSettings form = new ChangeSettings();
            form.ShowDialog();
        }

        private string GetConnectionString(string db) {
            if (chkIntegratedSecurity.Checked) {
                return factory.GetConnectionString(this.txtServer.Text, db);
            } else {
                return factory.GetConnectionString(this.txtServer.Text, db, txtUserName.Text, txtPassword.Text);
            }
            
        }

        private void btnLogin_Click(object sender, EventArgs e) {
            bindDatabases();
        }




    }
}